
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/03/2010 19:26:14
-- Generated from EDMX file: C:\VO\mainapp\VO.Data\ModelVO.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [VO2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_UserOwnedClub]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Clubs] DROP CONSTRAINT [FK_UserOwnedClub];
GO
IF OBJECT_ID(N'[dbo].[FK_ClubMembers_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClubMembers] DROP CONSTRAINT [FK_ClubMembers_User];
GO
IF OBJECT_ID(N'[dbo].[FK_ClubMembers_Club]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClubMembers] DROP CONSTRAINT [FK_ClubMembers_Club];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTrack]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tracks] DROP CONSTRAINT [FK_UserTrack];
GO
IF OBJECT_ID(N'[dbo].[FK_TrackCategoryTrack]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tracks] DROP CONSTRAINT [FK_TrackCategoryTrack];
GO
IF OBJECT_ID(N'[dbo].[FK_TrackTrackDone]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TrackDones] DROP CONSTRAINT [FK_TrackTrackDone];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTrackDone]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TrackDones] DROP CONSTRAINT [FK_UserTrackDone];
GO
IF OBJECT_ID(N'[dbo].[FK_ClubTrackDone]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TrackDones] DROP CONSTRAINT [FK_ClubTrackDone];
GO
IF OBJECT_ID(N'[dbo].[FK_UserUserBadge]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserBadges] DROP CONSTRAINT [FK_UserUserBadge];
GO
IF OBJECT_ID(N'[dbo].[FK_BadgeUserBadge]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserBadges] DROP CONSTRAINT [FK_BadgeUserBadge];
GO
IF OBJECT_ID(N'[dbo].[FK_TrackDoneUserBadge]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserBadges] DROP CONSTRAINT [FK_TrackDoneUserBadge];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[Clubs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Clubs];
GO
IF OBJECT_ID(N'[dbo].[Tracks]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tracks];
GO
IF OBJECT_ID(N'[dbo].[TrackCategories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TrackCategories];
GO
IF OBJECT_ID(N'[dbo].[TrackDones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TrackDones];
GO
IF OBJECT_ID(N'[dbo].[Badges]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Badges];
GO
IF OBJECT_ID(N'[dbo].[UserBadges]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserBadges];
GO
IF OBJECT_ID(N'[dbo].[ClubMembers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClubMembers];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserLogo] varbinary(max)  NULL,
    [Version] int  NOT NULL,
    [UserDescription] nvarchar(max)  NOT NULL,
    [LastEditDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [IdMembership] uniqueidentifier  NOT NULL,
    [PublicName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Clubs'
CREATE TABLE [dbo].[Clubs] (
    [Id] uniqueidentifier  NOT NULL,
    [UserOwnerId] int  NULL,
    [Version] int  NOT NULL,
    [ClubName] nvarchar(max)  NOT NULL,
    [ClubDescription] nvarchar(max)  NOT NULL,
    [ClubLogo] varbinary(max)  NULL,
    [LastEditDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL
);
GO

-- Creating table 'Tracks'
CREATE TABLE [dbo].[Tracks] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] int  NOT NULL,
    [TrackCategoryId] int  NOT NULL,
    [Version] int  NOT NULL,
    [Locked] bit  NOT NULL,
    [Level] int  NOT NULL,
    [LastEditDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [TrackName] nvarchar(max)  NOT NULL,
    [GeoString] nvarchar(max)  NULL,
    [TrackLength] int  NOT NULL,
    [IsValid] bit  NOT NULL
);
GO

-- Creating table 'TrackCategories'
CREATE TABLE [dbo].[TrackCategories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CategoryName] nvarchar(max)  NOT NULL,
    [Version] int  NOT NULL,
    [LastEditDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL
);
GO

-- Creating table 'TrackDones'
CREATE TABLE [dbo].[TrackDones] (
    [Id] uniqueidentifier  NOT NULL,
    [TrackId] uniqueidentifier  NOT NULL,
    [UserId] int  NULL,
    [ClubId] uniqueidentifier  NULL,
    [StartTime] datetime  NOT NULL,
    [EndTime] datetime  NOT NULL,
    [CompletedTime] bigint  NOT NULL,
    [PostedDate] datetime  NOT NULL,
    [IsValid] bit  NOT NULL
);
GO

-- Creating table 'Badges'
CREATE TABLE [dbo].[Badges] (
    [Id] uniqueidentifier  NOT NULL,
    [BadgeName] nvarchar(max)  NOT NULL,
    [BadgeLogo] varbinary(max)  NULL,
    [Version] int  NOT NULL,
    [LastEditDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [BadgeDescription] nvarchar(max)  NOT NULL,
    [OrderId] int  NOT NULL,
    [IsSystem] bit  NOT NULL
);
GO

-- Creating table 'UserBadges'
CREATE TABLE [dbo].[UserBadges] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] int  NULL,
    [BadgeId] uniqueidentifier  NULL,
    [EarnedDate] datetime  NOT NULL,
    [TrackDone_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'ClubMembers'
CREATE TABLE [dbo].[ClubMembers] (
    [UsersMembers_Id] int  NOT NULL,
    [ClubsMembership_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Clubs'
ALTER TABLE [dbo].[Clubs]
ADD CONSTRAINT [PK_Clubs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Tracks'
ALTER TABLE [dbo].[Tracks]
ADD CONSTRAINT [PK_Tracks]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TrackCategories'
ALTER TABLE [dbo].[TrackCategories]
ADD CONSTRAINT [PK_TrackCategories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TrackDones'
ALTER TABLE [dbo].[TrackDones]
ADD CONSTRAINT [PK_TrackDones]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Badges'
ALTER TABLE [dbo].[Badges]
ADD CONSTRAINT [PK_Badges]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserBadges'
ALTER TABLE [dbo].[UserBadges]
ADD CONSTRAINT [PK_UserBadges]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [UsersMembers_Id], [ClubsMembership_Id] in table 'ClubMembers'
ALTER TABLE [dbo].[ClubMembers]
ADD CONSTRAINT [PK_ClubMembers]
    PRIMARY KEY NONCLUSTERED ([UsersMembers_Id], [ClubsMembership_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [UserOwnerId] in table 'Clubs'
ALTER TABLE [dbo].[Clubs]
ADD CONSTRAINT [FK_UserOwnedClub]
    FOREIGN KEY ([UserOwnerId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserOwnedClub'
CREATE INDEX [IX_FK_UserOwnedClub]
ON [dbo].[Clubs]
    ([UserOwnerId]);
GO

-- Creating foreign key on [UsersMembers_Id] in table 'ClubMembers'
ALTER TABLE [dbo].[ClubMembers]
ADD CONSTRAINT [FK_ClubMembers_User]
    FOREIGN KEY ([UsersMembers_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ClubsMembership_Id] in table 'ClubMembers'
ALTER TABLE [dbo].[ClubMembers]
ADD CONSTRAINT [FK_ClubMembers_Club]
    FOREIGN KEY ([ClubsMembership_Id])
    REFERENCES [dbo].[Clubs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClubMembers_Club'
CREATE INDEX [IX_FK_ClubMembers_Club]
ON [dbo].[ClubMembers]
    ([ClubsMembership_Id]);
GO

-- Creating foreign key on [UserId] in table 'Tracks'
ALTER TABLE [dbo].[Tracks]
ADD CONSTRAINT [FK_UserTrack]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTrack'
CREATE INDEX [IX_FK_UserTrack]
ON [dbo].[Tracks]
    ([UserId]);
GO

-- Creating foreign key on [TrackCategoryId] in table 'Tracks'
ALTER TABLE [dbo].[Tracks]
ADD CONSTRAINT [FK_TrackCategoryTrack]
    FOREIGN KEY ([TrackCategoryId])
    REFERENCES [dbo].[TrackCategories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TrackCategoryTrack'
CREATE INDEX [IX_FK_TrackCategoryTrack]
ON [dbo].[Tracks]
    ([TrackCategoryId]);
GO

-- Creating foreign key on [TrackId] in table 'TrackDones'
ALTER TABLE [dbo].[TrackDones]
ADD CONSTRAINT [FK_TrackTrackDone]
    FOREIGN KEY ([TrackId])
    REFERENCES [dbo].[Tracks]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TrackTrackDone'
CREATE INDEX [IX_FK_TrackTrackDone]
ON [dbo].[TrackDones]
    ([TrackId]);
GO

-- Creating foreign key on [UserId] in table 'TrackDones'
ALTER TABLE [dbo].[TrackDones]
ADD CONSTRAINT [FK_UserTrackDone]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTrackDone'
CREATE INDEX [IX_FK_UserTrackDone]
ON [dbo].[TrackDones]
    ([UserId]);
GO

-- Creating foreign key on [ClubId] in table 'TrackDones'
ALTER TABLE [dbo].[TrackDones]
ADD CONSTRAINT [FK_ClubTrackDone]
    FOREIGN KEY ([ClubId])
    REFERENCES [dbo].[Clubs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClubTrackDone'
CREATE INDEX [IX_FK_ClubTrackDone]
ON [dbo].[TrackDones]
    ([ClubId]);
GO

-- Creating foreign key on [UserId] in table 'UserBadges'
ALTER TABLE [dbo].[UserBadges]
ADD CONSTRAINT [FK_UserUserBadge]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserUserBadge'
CREATE INDEX [IX_FK_UserUserBadge]
ON [dbo].[UserBadges]
    ([UserId]);
GO

-- Creating foreign key on [BadgeId] in table 'UserBadges'
ALTER TABLE [dbo].[UserBadges]
ADD CONSTRAINT [FK_BadgeUserBadge]
    FOREIGN KEY ([BadgeId])
    REFERENCES [dbo].[Badges]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BadgeUserBadge'
CREATE INDEX [IX_FK_BadgeUserBadge]
ON [dbo].[UserBadges]
    ([BadgeId]);
GO

-- Creating foreign key on [TrackDone_Id] in table 'UserBadges'
ALTER TABLE [dbo].[UserBadges]
ADD CONSTRAINT [FK_TrackDoneUserBadge]
    FOREIGN KEY ([TrackDone_Id])
    REFERENCES [dbo].[TrackDones]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TrackDoneUserBadge'
CREATE INDEX [IX_FK_TrackDoneUserBadge]
ON [dbo].[UserBadges]
    ([TrackDone_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------